package com.jiuyun.dao.impi;

import com.jiuyun.dao.SettingsDao;
import com.jiuyun.entity.Code;
import com.jiuyun.entity.Question;
import com.jiuyun.entity.Settings;
import com.jiuyun.entity.Subjec;
import com.jiuyun.util.AliPool;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class SettingsDaoimpl implements SettingsDao {
    static Connection conn;
    QueryRunner runner=new QueryRunner();
    PreparedStatement pst;


    @Override
    public Settings find(String bmid) throws SQLException {
        conn= AliPool.getConn();
        String sql="select exam.* from exam where exam_code=?";
        Settings s=runner.query(conn,sql,new BeanHandler<Settings>(Settings.class),bmid);
        conn.close();
        return s;
    }

    @Override
    public int upsuj(int fs,String bmid, int qid) throws SQLException {
        conn= AliPool.getConn();
        String sql="update subjec set question_fraction=? where code_id=" +
                "(select code_id from `code` where code_name=?) and question_id=?";
        int sum=runner.update(conn,sql,fs,bmid,qid);
        conn.close();
        return sum;
    }

    @Override
    public Code getbm(String bmid) throws SQLException {
        conn= AliPool.getConn();
        String sql="select * from code where code_name=?";
              Code list=runner.query(conn,sql,new BeanHandler<Code>(Code.class),bmid);
        conn.close();
        return list;
    }

    @Override
    public int add(Settings s) throws SQLException {
        conn= AliPool.getConn();
        String sql="insert into exam() VALUES(?,?,?,?,?,?,?,?,?,?,now())";
        Object zhi[]={s.getExam_id(),s.getExam_name(),s.getExam_avatar(),s.getExam_desc(),
                s.getExam_code(),s.getExam_score(),s.getExam_time_limit(),s.getExam_start_date(),
                s.getExam_end_date(),s.getExam_creator_id()};
        int sum=runner.update(conn,sql,zhi);
        conn.close();
        return sum;
    }

    @Override
    public int addcode(String bm) throws SQLException {
        conn= AliPool.getConn();
        String sql="insert into code() values(null,?)";
        int sum= runner.update(conn,sql,bm);
        conn.close();
        return sum;
    }

    @Override
    public int addSubjec(Subjec s) throws SQLException {
        conn= AliPool.getConn();
        String sql="insert into subjec values(null,?,?,?,null)";
        Object zhi[]={s.getCode_id(),s.getQuestion_id(),s.getQuestion_fraction()};
        int sum=runner.update(conn,sql,zhi);
        conn.close();
        return sum;
    }

    @Override
    public int up(int i,String bmid) throws SQLException {
        conn= AliPool.getConn();
        String sql="update exam set exam.exam_avatar=? where exam_code=?";
        int sum=runner.update(conn,sql,i,bmid);
        conn.close();
        return sum;
    }

    @Override
    public int del(String bmid, int qid) throws SQLException {
        conn= AliPool.getConn();
        String sql="DELETE from subjec where code_id=" +
                "(select code_id from `code` where code_name=?) and question_id=?";
        int sum=runner.update(conn,sql,bmid,qid);
        conn.close();
        return sum;
    }

    @Override
    public List<Question> gettimu(String qid) throws SQLException {
        conn= AliPool.getConn();
        String sql="select q.* from exam as e INNER JOIN  `code` as c on e.exam_code=c.code_name INNER JOIN subjec as s \n" +
                "\t\ton c.code_id=s.code_id INNER JOIN question as q on s.question_id=q.question_id\n" +
                "\t\t\twhere c.code_name=?";
        pst=conn.prepareStatement(sql);
        pst.setString(1,qid);
        ResultSet rs=pst.executeQuery();
        List<Question> list=new ArrayList<Question>();
        while (rs.next()){
            int q_id=rs.getInt(1);
            String q_name=rs.getString(2);
            int q_score=rs.getInt(3);
            int q_creator=rs.getInt(4);
            int q_type=rs.getInt(5);
            String q_a=rs.getString(6);
            String q_b=rs.getString(7);
            String q_c=rs.getString(8);
            String q_d=rs.getString(9);
            String q_answer=rs.getString(10);
            Date update_time=rs.getDate(11);
            Question q=new Question(q_id,q_name,q_score,q_creator,q_type,q_a,q_b,q_c,q_d,q_answer,update_time);
            list.add(q);
        }
        conn.close();
        return list;
    }

    @Override
    public List<Settings> getsj(int qid) throws SQLException {
        conn= AliPool.getConn();
        String sql="select *from exam where exam_creator_id=?";
        List<Settings> list=runner.query(conn,sql,new BeanListHandler<Settings>(Settings.class),qid);
        conn.close();
        return list;
    }

    @Override
    public List<Subjec> setm(String bmid) throws SQLException {
        conn= AliPool.getConn();
        String sql="select question_id from subjec where code_id=(select code_id from `code` where code_name=?)";
        List<Subjec> list=runner.query(conn,sql,new BeanListHandler<Subjec>(Subjec.class),bmid);
        conn.close();
        return list;
    }

    @Override
    public List<Question> alltimu(String bookname) throws SQLException {
        conn= AliPool.getConn();
        String sql="SELECT * FROM question WHERE question_type=(SELECT book_id from book WHERE book_name=?)";
        pst=conn.prepareStatement(sql);
        pst.setString(1,bookname);
        ResultSet rs=pst.executeQuery();
        List<Question> list=new ArrayList<Question>();
        while (rs.next()){
            int q_id=rs.getInt(1);
            String q_name=rs.getString(2);
            int q_score=rs.getInt(3);
            int q_creator=rs.getInt(4);
            int q_type=rs.getInt(5);
            String q_a=rs.getString(6);
            String q_b=rs.getString(7);
            String q_c=rs.getString(8);
            String q_d=rs.getString(9);
            String q_answer=rs.getString(10);
            Date update_time=rs.getDate(11);
            Question q=new Question(q_id,q_name,q_score,q_creator,q_type,q_a,q_b,q_c,q_d,q_answer,update_time);
            list.add(q);
        }
        conn.close();
        return list;
    }
}
